package zy.dao.money.access.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.money.access.AccessDAO;
import zy.dto.money.access.AccessListDto;
import zy.entity.money.access.T_Money_Access;
import zy.entity.money.access.T_Money_AccessList;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class AccessDAOImpl extends BaseDaoImpl implements AccessDAO{
	@Override
	public Integer count(Map<String, Object> params) {
		Object ac_ar_state = params.get("ac_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ac_manager = params.get("ac_manager");
		Object ac_number = params.get("ac_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_money_access t");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(ac_ar_state)) {
			sql.append(" AND ac_ar_state = :ac_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ac_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ac_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(ac_manager)) {
			sql.append(" AND ac_manager = :ac_manager ");
		}
		if (StringUtil.isNotEmpty(ac_number)) {
			sql.append(" AND INSTR(ac_number,:ac_number) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("ac_ca_id"))){
			sql.append(" AND ac_ca_id = :ac_ca_id ");
		}
		sql.append(" AND ac_shop_code = :shop_code ");//上级店铺编号
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Money_Access> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object ac_ar_state = params.get("ac_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ac_manager = params.get("ac_manager");
		Object ac_number = params.get("ac_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ac_id,ac_number,ac_maker,ac_manager,ac_date,ac_ar_state,ac_ar_date,ac_money,ac_remark,ac_sysdate,ac_shop_code,ac_us_id,ac_ca_id,companyid");
		sql.append(" FROM t_money_access t");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(ac_ar_state)) {
			sql.append(" AND ac_ar_state = :ac_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ac_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ac_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(ac_manager)) {
			sql.append(" AND ac_manager = :ac_manager ");
		}
		if (StringUtil.isNotEmpty(ac_number)) {
			sql.append(" AND INSTR(ac_number,:ac_number) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("ac_ca_id"))){
			sql.append(" AND ac_ca_id = :ac_ca_id ");
		}
		sql.append(" AND ac_shop_code = :shop_code ");//上级店铺编号
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ac_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Money_Access.class));
	}

	@Override
	public T_Money_Access load(Integer ac_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ac_id,ac_number,ac_maker,ac_manager,ac_date,ac_ar_state,ac_ar_date,ac_money,ac_remark,ac_sysdate,ac_shop_code,ac_us_id,ac_ca_id,companyid");
		sql.append(" FROM t_money_access t");
		sql.append(" WHERE ac_id = :ac_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ac_id", ac_id),
					new BeanPropertyRowMapper<>(T_Money_Access.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Money_Access load(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ac_id,ac_number,ac_maker,ac_manager,ac_date,ac_ar_state,ac_ar_date,ac_money,ac_remark,ac_sysdate,ac_shop_code,ac_us_id,ac_ca_id,companyid");
		sql.append(" FROM t_money_access t");
		sql.append(" WHERE ac_number = :ac_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ac_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Money_Access.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Money_Access check(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ac_id,ac_number,ac_maker,ac_manager,ac_date,ac_ar_state,ac_ar_date,ac_money,ac_remark,ac_us_id,ac_ca_id,companyid");
		sql.append(" FROM t_money_access t");
		sql.append(" WHERE ac_number = :ac_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ac_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Money_Access.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Money_AccessList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_ba_code,acl_type,acl_money,acl_remark,acl_us_id,companyid,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = acl_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name");
		sql.append(" FROM t_money_accesslist_temp t");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("acl_ca_id"))){
			sql.append(" AND acl_ca_id = :acl_ca_id");
		}
		if(StringUtil.isNotEmpty(params.get("acl_us_id"))){
			sql.append(" AND acl_us_id = :acl_us_id");
		}
		sql.append(" AND companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY acl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Money_AccessList.class));
	}

	@Override
	public List<String> temp_check(Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_ba_code");
		sql.append(" FROM t_money_accesslist_temp t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("acl_us_id", us_id).addValue("companyid", companyid), String.class);
	}
	
	@Override
	public List<String> temp_check_sell(Integer ca_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_ba_code");
		sql.append(" FROM t_money_accesslist_temp t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND acl_ca_id = :acl_ca_id");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("acl_ca_id", ca_id).addValue("companyid", companyid), String.class);
	}
	
	@Override
	public void temp_save(List<T_Money_AccessList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_money_accesslist_temp");
		sql.append(" (acl_ba_code,acl_type,acl_money,acl_remark,acl_us_id,acl_ca_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:acl_ba_code,:acl_type,:acl_money,:acl_remark,:acl_us_id,:acl_ca_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_updateMoney(T_Money_AccessList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_money_accesslist_temp");
		sql.append(" SET acl_money = :acl_money");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_id = :acl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateType(T_Money_AccessList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_money_accesslist_temp");
		sql.append(" SET acl_type = :acl_type");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_id = :acl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_updateRemark(T_Money_AccessList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_money_accesslist_temp");
		sql.append(" SET acl_remark = :acl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_id = :acl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_del(Integer acl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_money_accesslist_temp");
		sql.append(" WHERE acl_id=:acl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("acl_id", acl_id));
	}

	@Override
	public void temp_clear(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_money_accesslist_temp");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("acl_ca_id"))){
			sql.append(" AND acl_ca_id = :acl_ca_id");
		}
		if(StringUtil.isNotEmpty(params.get("acl_us_id"))){
			sql.append(" AND acl_us_id = :acl_us_id");
		}
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),params);
	}

	@Override
	public List<T_Money_AccessList> detail_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_number,acl_ba_code,acl_money,acl_remark,companyid,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = acl_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name");
		sql.append(" FROM t_money_accesslist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_number = :acl_number");
		sql.append(" AND companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY acl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Money_AccessList.class));
	}
	
	@Override
	public List<T_Money_AccessList> detail_list_forsavetemp(String ac_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_number,acl_ba_code,acl_money,acl_remark,companyid");
		sql.append(" FROM t_money_accesslist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_number = :acl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" ORDER BY acl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("acl_number", ac_number).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Money_AccessList.class));
	}
	
	@Override
	public void save(T_Money_Access access, List<T_Money_AccessList> details) {
		String prefix = CommonUtil.NUMBER_PREFIX_MONEY_ACCESS + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(ac_number))) AS new_number");
		sql.append(" FROM t_money_access");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(ac_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", access.getCompanyid()), String.class);
		access.setAc_number(new_number);
		sql.setLength(0);
		sql.append(" INSERT INTO t_money_access");
		sql.append(" (ac_number,ac_maker,ac_manager,ac_date,ac_ar_state,ac_ar_date,ac_money,ac_remark,ac_sysdate,ac_shop_code,ac_us_id,ac_ca_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ac_number,:ac_maker,:ac_manager,:ac_date,:ac_ar_state,:ac_ar_date,:ac_money,:ac_remark,:ac_sysdate,:ac_shop_code,:ac_us_id,:ac_ca_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(access),holder);
		access.setAc_id(holder.getKey().intValue());
		for(T_Money_AccessList item:details){
			item.setAcl_number(access.getAc_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_money_accesslist");
		sql.append(" (acl_number,acl_ba_code,acl_money,acl_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:acl_number,:acl_ba_code,:acl_money,:acl_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void update(T_Money_Access access, List<T_Money_AccessList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_money_access");
		sql.append(" SET ac_date=:ac_date");
		sql.append(" ,ac_maker=:ac_maker");
		sql.append(" ,ac_manager=:ac_manager");
		sql.append(" ,ac_money=:ac_money");
		sql.append(" ,ac_remark=:ac_remark");
		sql.append(" ,ac_ar_state=:ac_ar_state");
		sql.append(" ,ac_ar_date=:ac_ar_date");
		sql.append(" ,ac_us_id=:ac_us_id");
		sql.append(" ,ac_ca_id=:ac_ca_id");
		sql.append(" WHERE ac_id=:ac_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(access));
		for(T_Money_AccessList item:details){
			item.setAcl_number(access.getAc_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_money_accesslist");
		sql.append(" (acl_number,acl_ba_code,acl_money,acl_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:acl_number,:acl_ba_code,:acl_money,:acl_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateApprove(T_Money_Access access) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_money_access");
		sql.append(" SET ac_ar_state=:ac_ar_state");
		sql.append(" ,ac_ar_date = :ac_ar_date");
		sql.append(" WHERE ac_id=:ac_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(access));
	}
	
	@Override
	public List<AccessListDto> listBank4Approve(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ba_id,ba_code,ba_name,ba_balance,ba_shop_code,acl_money");
		sql.append(" FROM t_money_accesslist t");
		sql.append(" JOIN t_money_bank ba ON ba_code = acl_ba_code AND t.companyid = ba.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_number = :acl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("acl_number", number).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(AccessListDto.class));
	}
	
	
	@Override
	public void del(String ac_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_money_access");
		sql.append(" WHERE ac_number=:ac_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ac_number", ac_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_money_accesslist");
		sql.append(" WHERE acl_number=:acl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("acl_number", ac_number).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteList(String ac_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_money_accesslist");
		sql.append(" WHERE acl_number=:acl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("acl_number", ac_number).addValue("companyid", companyid));
	}
	
}
